package system.parser;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

/**
 * <b>Sys_SQLParser:</b><br>
 * Sys_SQLParser is using for parse a Java object to MySQL statements(Update or
 * Delete);<br>
 * @author Romam Law
 * @version 2.0.0
 */
public class Sys_SQLParser {

	/**
	 * Parse a Java model to a MySQL insert prepared-statement
	 * 
	 * @param model
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public PreparedStatement preInsertStatementParse(Object model,
			PreparedStatement preS) throws SQLException {
		Field[] fields = model.getClass().getDeclaredFields();
		// Filling values
		for (int i = 1; i <= fields.length; i++) {
			String fieldName = fields[i - 1].getName();
			String GetterName = "get"
					+ String.valueOf(fieldName.charAt(0)).toUpperCase()
					+ fieldName.substring(1);
			try {
				Object val = model.getClass().getDeclaredMethod(GetterName)
						.invoke(model);
				if (val != null) {
					preS = this.fillValue(val, preS, i);
				} else {
					preS = this.fillValue(null, preS, i);
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
		}
		return preS;
	}

	/**
	 * Parse a Java model to a MySQL update prepared-statement
	 * 
	 * @param model
	 * @param preS
	 * @param fieldNames
	 * @param Index
	 * @return
	 * @throws SQLException
	 */
	public PreparedStatement preUpdateStatementParse(Object model,
			PreparedStatement preS, String[] fieldNames, String[] Index)
			throws SQLException {
		// Filling values
		int paraOrder = 1;
		for (int i = 1; i <= fieldNames.length; i++, paraOrder++) {
			String fieldName = fieldNames[i - 1];
			String GetterName = "get"
					+ String.valueOf(fieldName.charAt(0)).toUpperCase()
					+ fieldName.substring(1);
			try {
				Object val = model.getClass().getDeclaredMethod(GetterName)
						.invoke(model);
				preS = this.fillValue(val, preS, paraOrder);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
		}
		for (int i = 1; i <= Index.length; i++, paraOrder++) {
			String fieldName = Index[i - 1];
			String GetterName = "get"
					+ String.valueOf(fieldName.charAt(0)).toUpperCase()
					+ fieldName.substring(1);
			try {
				Object val = model.getClass().getDeclaredMethod(GetterName)
						.invoke(model);
				preS = this.fillValue(val, preS, paraOrder);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
		}
		return preS;
	}

	public PreparedStatement preDeleteStatementParse(Object model,
			PreparedStatement preS, String[] Index) {
		for (int i = 1; i <= Index.length; i++) {
			String fieldName = Index[i - 1];
			String GetterName = "get"
					+ String.valueOf(fieldName.charAt(0)).toUpperCase()
					+ fieldName.substring(1);
			try {
				Object val = model.getClass().getDeclaredMethod(GetterName)
						.invoke(model);
				preS = this.fillValue(val, preS, i);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
		}
		return preS;
	}

	public String genPreInsertSql(Object model) {
		Field[] fields = model.getClass().getDeclaredFields();
		String SQL = "INSERT INTO " + model.getClass().getSimpleName()
				+ " (fieldNames) VALUES(vals)";
		String fieldNamesSet = "";
		String valsSet = "";
		for (int i = 0; i < fields.length; i++) {
			String fieldName = fields[i].getName();
			fieldNamesSet += "`" + fieldName + "`,";
			valsSet += "?,";
		}
		SQL = SQL.replace("fieldNames", fieldNamesSet.subSequence(0,
				fieldNamesSet.length() - 1));
		SQL = SQL.replace("vals", valsSet.subSequence(0, valsSet.length() - 1));
		return SQL;
	}

	public String genPreUpdateSql(Object model, String[] fieldNames,
			String[] Index) {
		String SQL = "UPDATE {?tb_name?} {?set?} {?where?}";
		String setStat = "SET ";
		String whereStat = "WHERE ";
		try {
			for (int i = 0; i < fieldNames.length; i++) {
				// Get field in the model class by its name
				Field field = model.getClass().getDeclaredField(fieldNames[i]);
				setStat += "`" + field.getName() + "`=?,";
			}
			if (Index != null || Index.length > 0) {
				for (int i = 0; i < Index.length; i++) {
					String ind = Index[i];
					whereStat += "`" + ind + "`=? AND ";
				}
				whereStat = whereStat.substring(0, whereStat.length() - 5);
			} else {
				whereStat = "";
			}
			setStat = setStat.substring(0, setStat.length() - 1);
			SQL = SQL.replace("{?tb_name?}", model.getClass().getSimpleName());
			SQL = SQL.replace("{?set?}", setStat);
			SQL = SQL.replace("{?where?}", whereStat);
		} catch (Exception e) {
			e.printStackTrace();
			SQL = null;
		}
		return SQL;
	}

	public String genPreDeleteSql(Object model, String[] Index) {
		String SQL = "DELETE FROM {?tb_name?} {?where?}";
		String whereStat = "WHERE ";
		try {
			if (Index != null || Index.length > 0) {
				for (int i = 0; i < Index.length; i++) {
					String ind = Index[i];
					whereStat += "`" + ind + "`=? AND ";
				}
				whereStat = whereStat.substring(0, whereStat.length() - 5);
			} else {
				whereStat = "";
			}
			SQL = SQL.replace("{?tb_name?}", model.getClass().getSimpleName());
			SQL = SQL.replace("{?where?}", whereStat);
		} catch (Exception e) {
			e.printStackTrace();
			SQL = null;
		}
		return SQL;
	}

	private PreparedStatement fillValue(Object o, PreparedStatement preState,
			int paraIndex) throws SQLException {
		preState.setObject(paraIndex, o);
		return preState;
	}

}
